USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_filegroup_name') BEGIN
	DROP PROCEDURE tsql.sp_filegroup_name
	PRINT '<<< DROP PROCEDURE tsql.sp_filegroup_name - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.sp_filegroup_name
Type:			User defined procedure 
Owner:			
Description:	returns the filegroup name for a given object in specifiedc database
Parameters:		@object_name = table/index name   
				@object_type = specifies in objet is table or index
				@parent_name = name of the parent object, schema for table and table for index
				@db_name = database where object resides
				@filegroup_name = OUTPUT of sproc
Dependens on:  	none
Usage:			DECLARE @fg_name sysname

				EXECUTE tsql.sp_filegroup_name  
				   @object_name = 'country'
				  ,@object_type = 'TABLE'
				  ,@parent_name = 'dbo'
				  ,@db_name = 'Lib'
				  ,@filegroup_name = @fg_name OUTPUT

				SELECT @fg_name 
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=sp_filegroup_name&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		06/22/2007 - Adrian E Dudau - Procedure created
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE PROCEDURE tsql.[sp_filegroup_name]
	(
	  @object_name SYSNAME
	, @object_type SYSNAME = 'TABLE'
	, @parent_name SYSNAME = 'dbo'
	, @db_name SYSNAME = NULL
	, @filegroup_name SYSNAME = '' OUTPUT  
	)
AS
BEGIN
	DECLARE @SQL AS NVARCHAR(MAX) 

	IF LEN(ISNULL(@db_name,'')) = 0 
		SET @db_name = DB_NAME(); 

	IF LEN(ISNULL(@parent_name,'')) = 0 
		SET @parent_name = 'dbo'; 
	
	IF LEN(ISNULL(@object_type,'')) = 0 
		SET @object_type = 'TABLE'; 
	
	
	IF UPPER(@object_type) IN('U','TABLE') BEGIN
		SET @SQL = 'SELECT @FileGroupName = f.name 
					FROM ' + @db_name + '.sys.tables t
					INNER JOIN ' + @db_name + '.sys.schemas s
						ON t.[schema_id] = s.[schema_id] 	
					INNER JOIN ' + @db_name + '.sys.indexes i
						ON t.[object_id] = i.[object_id] 
					INNER JOIN ' + @db_name + '.sys.filegroups f
						ON i.data_space_id = f.data_space_id
					WHERE i.[type] IN(0,1)	 
						AND s.name = ''' + @parent_name + '''
						AND t.name = ''' + @object_name + ''''
	END
	ELSE IF UPPER(@object_type) IN('I','INDEX') BEGIN
		SET @SQL = '	SELECT @FileGroupName = f.name
					FROM ' + @db_name + '.sys.indexes i
					INNER JOIN ' + @db_name + '.sys.filegroups f
						ON i.data_space_id = f.data_space_id
					WHERE i.[name] = ''' + @object_name + ''' 
						AND [object_id] = OBJECT_ID(''' + @parent_name + ''') '	
	END
	ELSE 
		SET @SQL = '	SELECT @FileGroupName = NULL'
	
	
	EXECUTE sp_executesql @SQL, N'@FileGroupName SYSNAME OUTPUT', @FileGroupName = @filegroup_name OUTPUT;
	RETURN 0	
	
END
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_filegroup_name') BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_filegroup_name - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_filegroup_name - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

--  GRANT SELECT TO PUBLIC will be used at Db level
